import Core.DataBase as DataBase
import Core.Gadget as Gadget
import mysql.connector
from mysql.connector import errorcode
from datetime import date, datetime, timedelta
from copy import copy
from Core.DataSeries import DataSeries
import pandas as pd
import time

import numpy as np

class MySQLDB(DataBase.DataBase):

    def __init__(self, address, port, username="root", password=None):
        self.name = "MySQLDB 普通MYSQL数据库"
        print("建立数据库对象", self.name)
        self.address = address
        self.port = port
        self.username = username
        self.password = password
        self.contexts = {}

    def _Connect(self, databaseName):
        #
        print("Connecting MySQL")
        context = mysql.connector.connect(user=self.username,
                                          password=self.password,
                                          db=databaseName,
                                          host=self.address,
                                          buffered=True)  # 占用大量内存，但是避免handle_unread_result错误
        self.contexts[databaseName] = context
        print("Connected MySQL", databaseName)
        return context

    def _TryToConnect(self, databaseName):
        context = None
        try:
            context = self._Connect(databaseName)
        except mysql.connector.Error as err:
            if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
                print("Something is wrong with your user name or password")
            elif err.errno == errorcode.ER_BAD_DB_ERROR:
                print("Database does not exist")
            else:
                print(err, "err.errno", err.errno)
        #
        return context

    def _TestConnect(self, databaseName):
        context = self.contexts[databaseName]
        try:
            context.ping()
            return context
        except:
            print("Database connection has break, try to Re-connect...")
            context = self._Connect(databaseName)
            return context

    def Close(self):
        for key, value in self.contexts.items():
            value.close()

    def _CommonSQLCommand(self, command, tableName, document):
        sqlCommand = command + " " + tableName + " "
        headers = ""
        values = ""
        count = 0
        for key, value in document.items():
            count += 1
            if count > 1:
                headers += ","
                values += ","
            headers += key
            values += "%(" + key + ")s"
        #
        headers = "(" + headers + ")"
        #
        values = "VALUES (" + values + ")"
        #
        sqlCommand = sqlCommand + " " + headers + " " + values
        return sqlCommand

    def _InsertSQLCommand(self, tableName, document):
        return self._CommonSQLCommand("INSERT INTO", tableName, document)

    def _UpsertSQLCommand(self, tableName, document):
        return self._CommonSQLCommand("REPLACE INTO", tableName, document)

    def _ConditionSQLCommand(self, ofilter):
        # ---avoid delete---
        filter = copy(ofilter)
        limitStatement = ""
        orderbyStatement = ""
        statement = None
        #
        if isinstance(filter, dict):
            # ---limit / skip---
            if "skip" in filter and "limit" in filter:
                limitStatement = " limit " + str(filter["skip"]) + "," + str(filter["limit"])
                del filter["limit"]
                del filter["skip"]
            #
            if "limit" in filter:
                limitStatement = " limit " + str(filter["limit"])
                del filter["limit"]
            #
            if "orderby" in filter:
                orderby = filter["orderby"]
                orderbyStatement = self._OrderBySQLCommand(orderby)
                del filter["orderby"]

            # ---Where condition---
            if len(filter) != 0: # 仍然还有元素
                statement = self._WhereSQLCommand_Dict(filter, "and")

        elif isinstance(filter, list):
            limit = None
            skip = None
            for condition in filter:
                name = condition[0]
                value = condition[1]
                #
                if name == "limit":
                    limitStatement = " limit " + str(value)
                elif name == "skip":
                    skip = value
                elif name == "orderby":
                    orderbyStatement = self._OrderBySQLCommand(value)

            # End of Conditions Loop
            if skip != None and limit != None:
                limitStatement = " limit " + str(skip) + "," + str(limit)
            #
            # and xxx > aaa
            # or xxx = ccc
            statement = self._WhereSQLCommand_List(filter)

        #
        whereStatment = ""
        if statement != None:
            whereStatment = " Where " + statement
        #
        return whereStatment + " " + orderbyStatement + " " + limitStatement

    def _OrderBySQLCommand(self, orderbyFilter):
        def ProcessTurple():
            pass
        def ProcessDict():
            pass
        #
        orderbyStatement = ""
        count = 0
        if not isinstance(orderbyFilter, list):
            orderbyFilter = [orderbyFilter]

        for item in orderbyFilter:
            if isinstance(item, tuple):
                field = item[0]
                value = item[1]
            elif isinstance(item, dict):
                a = list(item.keys())
                field = a[0]
                value = item[field]
            #
            count += 1
            if count == 1:
                orderbyStatement += " Order By "
            if count > 1:
                orderbyStatement += ","
            orderbyStatement += field
            if value == -1:
                orderbyStatement += " Desc "
        #
        return orderbyStatement

    def _QuerySQLCommand(self, tableName, projection={}, filter={}):
        # ---Process Projection---
        header = "*"
        if len(projection) != 0:
            header = ""
            count = 0
            if isinstance(projection, dict): # Loop Dict
                for key, value in projection.items():
                    if value == 1:
                        if count > 0:
                            header += ","
                        header += key
                        count += 1
            #
            elif isinstance(projection, list): # Loop List
                for item in projection:
                    if count > 0:
                        header += ","
                    header += item
                    count += 1
        # ---Distinct---
        if "distinct" in filter:
            header = "distinct(" + filter["distinct"] + ")"
            del filter["distinct"]
        #
        conditionStatement = self._ConditionSQLCommand(filter)
        #
        sqlCommand = "Select " + header + " From " + tableName + " " + conditionStatement
        return sqlCommand

    def _UpdateSQLCommand(self, tableName, target, document):
        #
        statement1 = ""
        count = 0
        for key, value in document.items():
            count += 1
            if count > 1:
                statement1 += ","
            statement1 += key + "=" + "%(" + key + ")s"

        statement2 = self._WhereSQLCommand(target)
        #
        sqlCommand = "Update " + tableName + " Set " + statement1 + " Where " + statement2
        return sqlCommand

    def _WhereSQLCommand_List(self, list):
        #
        statement = None
        for condition in list:
            if condition[0] in ["limit", "skip", "orderby"]:
                continue
            if condition[0] == "or" or condition[0] == "and":
                if len(condition) == 3:
                    logic = condition[0]
                    field = condition[1]
                    op = "="
                    value = condition[2]
                elif len(condition) == 4:
                    logic = condition[0]
                    field = condition[1]
                    op = condition[2]
                    value = condition[3]
                else:
                    continue
            else: # 默认 and
                logic = "and"
                if len(condition) == 2:
                    field = condition[0]
                    op = "="
                    value = condition[1]
                elif len(condition) == 3:
                    field = condition[0]
                    op = condition[1]
                    value = condition[2]
                else:
                    continue
            #
            strCondition = field + " " + op + " " + "\"" + str(value) + "\""
            if statement == None:
                statement = strCondition
            else:
                statement += " " + logic + " " + strCondition
        #
        return statement

    # ---Example---
    # filter["DateTime"] = {">=": datetime1, "<=": datetime2}
    # filter["Symbol"] = "000001.SH"
    def _WhereSQLCommand_Dict(self, filter, andor="and"):
        statement = ""
        conditions = []
        for key, value in filter.items():
            # statement += key + " = %(" + key + ")s"
            # 是否有嵌套条件
            if isinstance(value, dict):
                s = self._WhereRangeCondition(key, value, andor="and")
                conditions.append(s)
            else:
                conditions.append(key + " = " + "\"" + str(value) + "\"")
        #
        hyphen = " " + andor + " "
        statement = hyphen.join(conditions)
        return statement

    # ---filter["Date"] = {">=": datetime1.date(), "<=": datetime2.date()}---
    # ---filter["term"] = {">": 0, "<": 730}---
    def _WhereRangeCondition(self, field, filter, andor="and"):
        conditions = []
        for gtlt, value in filter.items():
            if gtlt in [">=", ">", "<", "<="]:
                try:
                    num = float(value)
                    conditions.append(field + " " + gtlt + " " + str(num))
                except:
                    conditions.append(field + " " + gtlt + " " + "\"" + str(value) + "\"")
        #
        hyphen = " " + andor + " "
        statement = hyphen.join(conditions)
        return statement


    def _WhereSQLCommand(self, filter, andor="and"):
        statement = ""
        if isinstance(filter, list):
            statement = self._WhereSQLCommand_List(filter, andor)
        elif isinstance(filter, dict):
            statement = self._WhereSQLCommand_Dict(filter, andor)
        #
        return statement

    def _InsertIfNotExistSQLCommand(self, tableName, target, document):
        #
        headers = ""
        values = ""
        count = 0
        for key, value in document.items():
            count += 1
            if count > 1:
                headers += ","
                values += ","
            headers += key
            values += "%(" + key + ")s"

        #
        whereStatement = self._WhereSQLCommand(target)

        #
        sqlCommand = "INSERT INTO " + tableName \
                     + " (" + headers + ") Select " + values \
                     + " FROM dual WHERE not exists "\
                     + " (SELECT * from " + tableName \
                     + " Where " + whereStatement + " )"

        return sqlCommand

    def _SQLInsertBar(self, document):
        pass

    def _SQLInsertSymbol(self, document):
        pass

    def _ExecuteSQL(self, databaseName, tableName, document, sqlCommand, commit=True):
        #
        databaseName = databaseName.lower()
        tableName = tableName.lower()
        #
        context = self.contexts.get(databaseName)
        if context is None:
            context = self._Connect(databaseName)
        #
        try:
            cursor = context.cursor()
        except mysql.connector.Error as err:
            context = self._TryToConnect(databaseName)
            cursor = context.cursor()

        if isinstance(document, list):
            cursor.executemany(sqlCommand, document)
        else:
            cursor.execute(sqlCommand, document)

        if commit:
            context.commit()
        return cursor

    def ExecuteSQL(self, databaseName, sqlCommand, commit=True):
        c = self._ExecuteRawSQL(databaseName, sqlCommand, commit)
        return c._rows

    def _ExecuteRawSQL(self, databaseName, sqlCommand, commit=True):
        #
        databaseName = databaseName.lower()

        # Connection
        #
        context = self.contexts.get(databaseName)
        if not context:
            context = self._Connect(databaseName)
        #
        try:
            cursor = context.cursor()
        except mysql.connector.Error as err:
            context = self._TryToConnect(databaseName)
            cursor = context.cursor()

        # Execute
        cursor.execute(sqlCommand)

        # Commit
        if commit:
            context.commit()
        return cursor

    def _CheckField(self, document):
        if "_id" in document:
            del document["_id"]
        #
        if "Key" in document:
            document["Key2"] = document["Key"]
            del document["Key"]
        #
        for key, value in document.items():
            #
            if str(value) in ["nan", "NaT", "inf"]:
                document[key] = None
            #
            if isinstance(value, datetime):
                if (str(value) != 'NaT'):
                    document[key] = document[key].strftime("%Y-%m-%d %H:%M:%S")
        #
        return document

    # 和mysql保留字冲突的表名
    def _CheckTablename(self, tableName):
        #
        # if tableName.lower() == "index":
        #     tableName = "Index2"
        #
        # if tableName.lower() == "option": # ??
        #     tableName = "Option2"
        #
        return tableName


    # 为了兼容旧代码，需要重定位数据库和表名
    def _ReDirectDatabaseAndTable(self, databaseName, tableName):

        if tableName == "index_daily_bar":
            tableName = "index_dailybar"
        if tableName == "stock_daily_bar":
            tableName = "stock_dailybar"
        if tableName == "future_daily_bar":
            tableName = "future_dailybar"

        #
        if tableName in ["portfolio_instrument", "hedgefund_instrument", "spv_instrument"]:
            return databaseName, tableName
        #
        if databaseName.lower() == "dailybar":
            return "financial_data", tableName + "_dailybar"
        #
        if databaseName.lower() == "instruments":
            if tableName == None or tableName == "":
                return "financial_data", "Instrument"
            return "financial_data",  "Instrument_" + tableName
        #
        if databaseName.lower() == "stock":
            return "financial_data", "Stock_" + tableName
        #
        if databaseName.lower() == "mutualfund":
            return "financial_data", "mutualfund_" + tableName
        #
        if databaseName.lower() == "bond":
            return "financial_data", "bond_" + tableName
        #
        if databaseName.lower() == "future":
            return "financial_data", "future_" + tableName
        #
        if databaseName.lower() == "index":
            return "financial_data", "index_" + tableName
        #
        if databaseName.lower() == "option":
            return "financial_data", "option_" + tableName

        # 兼容 stock_instrument
        # instrument_stock
        table_names = tableName.split("_")
        tableName = tableName
        if len(table_names) == 2 and table_names[1] == "instrument":
            tableName = "instrument_" + table_names[0]

        #
        return databaseName, tableName


    def Count(self , databaseName, tableName, filter={}):
        context = self.contexts.get(databaseName)
        if not context:
            context = self._Connect(databaseName)

        if tableName.lower() == "index":
            tableName = "index2"

        #
        # conditionStatement = self._QuerySQLCommand(tableName, filter=filter)
        # sqlCommand = "Select Count(1) From ( " + conditionStatement + ") tmp"

        conditionStatement = self._ConditionSQLCommand(filter)
        sqlCommand = "Select Count(1) From " + tableName + conditionStatement
        cursor = context.cursor()
        cursor.execute(sqlCommand)

        count = 0
        for item in cursor:
            count = item[0]
        cursor.close()
        return count

    # 排重并按照规则排序
    def GroupBySort(self, databaseName, tableName, fields=[], groupby=[], sortby={}):

        fieldStatement = ""
        count = 0
        for field in fields:
            if field not in sortby.keys():
                count += 1
                if count > 1:
                    fieldStatement += ","
                fieldStatement += field

        #
        sortStatement = ""
        count = 0
        for key, value in sortby.items():
            count += 1
            if count > 1:
                sortStatement += ","
            if value == 1:
                sortStatement += "max(" + key + ") as " + key
            else:
                sortStatement += "min(" + key + ") as " + key

        #
        groupStatement = ""
        count = 0
        for field in groupby:
            count += 1
            if count > 1:
                groupStatement += ","
            groupStatement += field

        # sqlCommand = "SELECT tag_id, tag, max(modified_time) from rec_article.tag_idf GROUP BY tag_id"
        sqlCommand = "Select " + fieldStatement + "," + sortStatement + " From " + tableName + " Group by " + groupStatement

        #
        cursor = self._ExecuteSQL(databaseName, tableName, {}, sqlCommand, commit=False)
        headers = cursor.column_names
        documentList = []
        for item in cursor:
            document = {}
            for i in range(len(item)):
                document[headers[i]] = item[i]
            documentList.append(document)
        #
        cursor.close()
        return documentList

    def FindWithSQL(self, databaseName, tableName, sqlCommand):
        #
        cursor = self._ExecuteSQL(databaseName, tableName, {}, sqlCommand, commit=False)

        #
        # results = cursor.fetchall()
        headers = cursor.column_names
        documentList = []
        for item in cursor:
            document = {}
            for i in range(len(item)):
                document[headers[i]] = item[i]

            #
            if "Key2" in document:
                document["Key"] = document["Key2"]
                del document["Key2"]

            #
            documentList.append(document)
        #
        cursor.close()
        return documentList

    # filter 兼容 [] and {},
    # [] 适用于重名条件，比如Date 大于datetime1 同时又小于datetime2 （SQL里的Between）
    def Find(self, databaseName, tableName, filter={}, sort=[], projection={}):
        #
        tableName = self._CheckTablename(tableName)
        databaseName, tableName = self._ReDirectDatabaseAndTable(databaseName, tableName)
        #
        if len(sort) > 0:
            if isinstance(filter, dict):
                filter["orderby"] = sort
            elif isinstance(filter, list):
                filter.append(("orderby", sort))

        # skip / limit in Query SQL Command
        sqlCommand = self._QuerySQLCommand(tableName, projection, filter)

        #
        return self.FindWithSQL(databaseName, tableName, sqlCommand)


    def Insert(self, databaseName, tableName, document):
        tableName = self._CheckTablename(tableName)
        sqlCommand = self._InsertSQLCommand(tableName, document)
        cursor = self._ExecuteSQL(databaseName, tableName, document, sqlCommand)
        cursor.close()


    def Insert_Many(self, databaseName, tableName, documents):
        tableName = self._CheckTablename(tableName)
        document = documents[0]
        sqlCommand = self._InsertSQLCommand(tableName, document)
        cursor = self._ExecuteSQL(databaseName, tableName, documents, sqlCommand)
        cursor.close()


    def InsertIfNotExisted(self, databaseName, tableName, target, document):
        sqlCommand = self._InsertSQLCommand(tableName, document)
        cursor = self._ExecuteSQL(databaseName, tableName, document, sqlCommand)
        cursor.close()


    def Upsert(self, databaseName, tableName, target, document):
        #
        tableName = self._CheckTablename(tableName)
        #
        document = self._CheckField(document)
        #
        sqlCommand = self._UpsertSQLCommand(tableName, document)
        cursor = self._ExecuteSQL(databaseName, tableName, document, sqlCommand)
        cursor.close()

    def UpsertOld(self, databaseName, tableName, target, document):
        #
        # ---临时替代法---
        sqlCommand = self._QuerySQLCommand(tableName, filter=target)
        cursor = self._ExecuteSQL(databaseName, tableName, target, sqlCommand, commit=False)
        #
        for item in cursor:
        #   print(item)
            pass
        cursor.close()
        #
        rowCount = cursor._rowcount

        if rowCount > 0:
            # ---Update---
            self.Update(databaseName, tableName, target, document)
        else:
            # ---Insert---
            a= 1
            self.Insert(databaseName, tableName, document)


    def Upsert_Many(self, databaseName, tableName, targets, documents):
        #
        tableName = self._CheckTablename(tableName)
        #
        if len(documents) == 0:
            return
        #
        document = documents[0]
        for d in documents:
            self._CheckField(d)
        #
        sqlCommand = self._UpsertSQLCommand(tableName, document)
        cursor = self._ExecuteSQL(databaseName, tableName, documents, sqlCommand)
        cursor.close()

    def Update(self, databaseName, tableName, target, document):
        #
        tableName = self._CheckTablename(tableName)
        document = self._CheckField(document)
        target = self._CheckField(target)
        #
        sqlCommand = self._UpdateSQLCommand(tableName, target, document)
        # print(sqlCommand)
        cursor = self._ExecuteSQL(databaseName, tableName, document, sqlCommand)
        cursor.close()

    def Update_Many(self, databaseName, tableName, targets, documents):
        #
        tableName = self._CheckTablename(tableName)
        #
        if len(documents) == 0:
            return
        #
        document = documents[0]
        for d in documents:
            self._CheckField(d)
        #
        # sqlCommand = self._UpdateSQLCommand(tableName, {"key2":} ,document)
        # cursor = self._ExecuteSQL(databaseName, tableName, documents, sqlCommand)
        # cursor.close()

    def SaveDataFrame(self, databaseName, tableName, df, save_fields=[]):
        #
        if "Key2" in df.columns:
            df.rename(columns={"Key2":"key2"}, inplace=True)
        #
        if "key2" not in df.columns:
            print("Must Specify 'key2' Field !!!")
            return
        #
        if len(save_fields) == 0:
            save_fields = df.columns
        #
        if "key2" not in save_fields:
            save_fields.append("key2")

        # Check field exist
        for field in save_fields:
            if field not in df.columns:
                print("Missing Field when Save DataFrame !!!", field)
                return

        # Saving the Columns in save fields ONLY
        df_saving = df[save_fields]

        # Change DataFrame to Documents
        new_documents = df_saving.to_dict(orient="records")

        # 解决nan NaT无法储存的问题
        for document in new_documents:
            for key, value in document.items():
                if str(value) in ["nan", "NaT", "inf"]:
                    document[key] = None

        #
        self.Upsert_Many(databaseName, tableName, [], new_documents)
        print("saved df #element", len(new_documents))

    def GetDataFrame(self, databaseName, tableName, filter=[], projection=[],
                     datetime1=None, datetime2=None, sort=None, date_field="datetime"):
        #
        if datetime1:
            filter.append(("datetime", ">=", datetime1))
        if datetime2:
            filter.append(("datetime", "<=", datetime2))

        if sort == None and (datetime1 or datetime2):
            sort = [("datetime", 1)]
        elif sort == None:
            sort = []
        else:
            pass

        #
        if len(projection) > 0:
            documents = self.Find(databaseName, tableName, filter=filter, projection=projection, sort=sort)
        else:
            documents = self.Find(databaseName, tableName, filter=filter, sort=sort)
        #
        if len(documents) == 0:
            return pd.DataFrame()
        #
        df = Gadget.DocumentsToDataFrame(documents)
        #
        if "date" in df.columns:
            df["date"] = pd.to_datetime(df["date"])
        #
        if "datetime" in df.columns:
            df["datetime"] = pd.to_datetime(df["datetime"])
        #
        # if datetime1 and datetime2:
        #     df = df[(df[date_field] >= datetime1) & (df[date_field] <= datetime2)]
        # elif datetime1:
        #     df = df[df[date_field] >= datetime1]
        # elif datetime2:
        #     df = df[df[date_field] <= datetime2]
        #
        return df

    def GetDataFrame2(self, symbol, dataType=None, instrumentType=None, datetime1=None, datetime2=None):
        #
        databaseName = "DailyBar"
        tableName = instrumentType
        #
        if dataType == "DailyBar":
            databaseName = "DailyBar"
        #
        sort = [("DateTime", 1)]
        documents = self.Find(databaseName, tableName, filter={"Symbol": symbol}, sort=sort)
        df = Gadget.DocumentsToDataFrame(documents)
        #
        if "Date" in df.columns:
            df["Date"] = pd.to_datetime(df["Date"])
        #
        if "DateTime" in df.columns:
            df["DateTime"] = pd.to_datetime(df["DateTime"])
        #
        if datetime1 and datetime2:
            df = df[(df["DateTime"] >= datetime1) & (df["DateTime"] <= datetime2)]
        elif datetime1:
            df = df[df["DateTime"] >= datetime1]
        elif datetime2:
            df = df[df["DateTime"] <= datetime2]
        #
        return df

    def GetDataSeries(self, symbol, dataType=None, instrumentType=None, datetime1=None, datetime2=None):
        #
        filter = {}
        filter["Symbol"] = symbol
        #
        if datetime1 != None and datetime2 != None:
            filter["DateTime"] = {">=": datetime1, "<=": datetime2}
        elif datetime1 != None:
            filter["DateTime"] = {">=": datetime1}
        elif datetime2 != None:
            filter["DateTime"] = {"<=": datetime2}
        #
        sort = [("DateTime", 1)]
        if dataType == "DailyBar":
            databaseName = "DailyBar"

        if instrumentType == "Index":
            # tableName = "Index2"
            tableName = instrumentType
            pass
        else:
            tableName = instrumentType

        # ---Load Database---
        series = self.Find(databaseName, tableName, filter, sort)

        # ---Build DataSeries Object---
        dataSeries = DataSeries(symbol + "_Time_86400_Bar")
        for d in series:
            # print(document)
            if "_id" in d:
                del d["_id"]
            dataSeries.Add(d)
        #
        return dataSeries

    def SaveDataSeries(self, dataSeries, dataType=None, instrumentType=None, isUpSert=True):
        #
        dataSeriesName = dataSeries.model_name

        #
        print(str(datetime.now()), " Saving DataSeries" , dataSeriesName)
        if dataSeries.Count() == 0:
            print(dataSeriesName + " SaveDataSeries::No Data to Save")
            return

        symbol = DataSeries.ParseSymbol(dataSeriesName)
        name = DataSeries.ParseDataName(dataSeriesName)
        if dataType == None:
            dataType = DataSeries.ParseDataType(dataSeriesName)
        else:
            dataType = dataType.lower()
        #
        instrumentType = instrumentType.lower()

        documents = []
        for item in dataSeries.items:
            document = copy(item)
            document["Key2"] = item["Key"]
            del document["Key"]
            #
            if "Values" in item:
                del document["Values"]
            #
            if "DataSeries" in item:
                del document["DataSeries"]
            #
            documents.append(document)

        #
        if dataType.lower() == "dailybar":
            self.Upsert_Many(dataType, instrumentType, {}, documents)
        #
        elif dataType.lower() == "factor":
            tablename = name
            self.Upsert_Many("factor", tablename, {}, documents)
        #
        print(str(datetime.now()), "Saved DataSeries:", dataSeriesName, "#Elements", str(dataSeries.Count()))

    def GetCollectionNames(self, databaseName, pattern=None):
        cursor = self._ExecuteRawSQL(databaseName, "Show tables", commit=True)
        documentList = []
        for item in cursor:
            name = item[0]
            if pattern == None:
                documentList.append(name)
            elif pattern.lower() in name:
                documentList.append(name)
        #
        cursor.close()
        return documentList

    def Get_Instruments(self, instrument_type, filter=[], projection=[], listed=True):
        instruments = self.Find("financial_data", "Instrument_" + instrument_type, filter=filter, projection=projection)
        return instruments

    def Get_Instruments_DataFrame(self, instrument_type, filter=[], projection=[], listed=True):
        # filter.append(("is_delete", 0))
        df_instruments = self.GetDataFrame("financial_data", "Instrument_" + instrument_type, filter=filter, projection=projection, sort=[])
        return df_instruments

    def Get_Daily_Bar(self, symbol, instrument_type, projection=[], datetime1=None, datetime2=None):
        # New Style
        df_bars = self.Get_Daily_Bar_DataFrame(symbol, instrument_type, projection=projection,
                                               datetime1=datetime1, datetime2=datetime2)
        documents = df_bars.to_dict(orient='records')
        return documents

    def Get_Daily_Bar_DataFrame(self, symbol, instrument_type, projection=[], datetime1=None, datetime2=None):
        # print("---MySQLDB::Get_Daily_Bar_DataFrame---", symbol, instrument_type, datetime1, datetime2)
        return self.GetDataFrame("financial_data", instrument_type + "_dailybar", filter=[("symbol", symbol)],
                                 projection=projection,
                                 datetime1=datetime1, datetime2=datetime2, date_field="date", sort=[("date",1)])

    def Delete(self, databaseName, tableName, filter):
        if len(filter) == 0:
            return
        tableName = self._CheckTablename(tableName)
        where_statement = self._ConditionSQLCommand(filter)
        #
        sqlCommand = "Delete From " + tableName + " " + where_statement
        self._ExecuteSQL(databaseName, tableName, {}, sqlCommand, commit=True)


